package laugh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import laugh.pojo.Content;
import laugh.pojo.ExtendedContent;
import laugh.pojo.OptResultInfo;
import laugh.util.Constants;
import laugh.util.DateUtil;
import laugh.util.db.C3p0Util;

/**
 * 
*    
* 项目名称：LaughFactory   
* 类名称：ContentDao   
* 类描述： 内容Dao 
* 创建人：wizard   
* 创建时间：2012-3-30 下午03:15:30   
* 修改人：wizard   
* 修改时间：2012-3-30 下午03:15:30   
* 修改备注：   
* @version 0.9
*
 */
public class ContentDao extends BaseDao {

    /**
     * 检查是否已发布过类似内容
     * 
     * @param md5
     * @return boolean
     */
    public boolean checkContentExist(String md5) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        boolean flag = false;
        try {
            conn = C3p0Util.getConn();
            String sql = "select 1 from content where md5=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, md5);
            rs = pst.executeQuery();
            if (rs.next()) {
                flag = true;
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return flag;
    }

    /**
     * 添加一条内容记录
     * 
     * @param content
     * @return OptResultInfo
     */
    public OptResultInfo addContent(Content content) {
        OptResultInfo optResultInfo = new OptResultInfo();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "insert into content values(null,?,null,?,?,?,now(),now(),0,0,0)";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, content.getUid());
            pst.setString(2, content.getContent());
            pst.setString(3, content.getKeywords());
            pst.setString(4, content.getMd5());
            pst.executeUpdate();
            pst.close();
            optResultInfo.setCode(1);// 添加成功
            optResultInfo.setInfo(1L);//
            optResultInfo.setMeg("添加成功");
        } catch (SQLException e) {
            optResultInfo.setCode(0);// 添加失败
            optResultInfo.setInfo(1L);//
            optResultInfo.setMeg("数据库出错,添加失败");
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return optResultInfo;
    }

    /**
     * 删除一条内容记录
     * 
     * @param id
     * @return OptResultInfo
     */
    public OptResultInfo deleteContent(Long id) {
        OptResultInfo optResultInfo = new OptResultInfo();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "delete from content where id=?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, id);
            pst.executeUpdate();
            pst.close();
            optResultInfo.setCode(1);// 删除成功
            optResultInfo.setInfo(1L);//
            optResultInfo.setMeg("删除成功");
        } catch (SQLException e) {
            optResultInfo.setCode(0);// 删除失败
            optResultInfo.setInfo(1L);//
            optResultInfo.setMeg("数据库出错,删除失败");
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return optResultInfo;
    }

    /**
     * 查询一条内容
     * 
     * @param id
     * @return Content
     */
    public Content loadContent(Long id) {
        Connection conn = null;
        PreparedStatement pst = null;
        Content content = new Content();
        try {
            conn = C3p0Util.getConn();
            String sql = "select id,uid,content,p_time,up,down,score from content where id=?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, id);
            ResultSet resultSet = pst.executeQuery();
            if (resultSet.next()) {
                content.setId(id);
                content.setUid(resultSet.getLong("uid"));
                content.setContent(resultSet.getString("content"));
                content.setP_time(DateUtil.timestamp2String(resultSet
                        .getTimestamp("p_time")));
                content.setUp(resultSet.getInt("up"));
                content.setDown(resultSet.getInt("down"));
                content.setScore(resultSet.getInt("score"));
            }
            resultSet.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return content;
    }

    /**
     * 获取id小于max_id的最新(Constents.CONTENT_PERTIME_NUM条)的内容
     * 用于未登录用户
     * 
     * @param max_id
     * @return List<ExtendedContent>
     */
    public List<ExtendedContent> loadSpecialContent(Long max_id) {
        List<ExtendedContent> result = new ArrayList<ExtendedContent>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "SELECT b.content_num, b.f_num, b.id as uid, b.login_id, b.pic_url,a.score, a.id as cid, a.content, a.up, a.down, a.p_time FROM `content` a, `user` b WHERE a.uid = b.id AND a.id < ? ORDER BY a.id DESC LIMIT 0, ?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, max_id);
            pst.setInt(2, Constants.CONTENT_PERTIME_NUM);
            ResultSet resultSet = pst.executeQuery();
            ExtendedContent content;
            while (resultSet.next()) {
                content = new ExtendedContent();
                content.setCid(resultSet.getLong("cid"));
                content.setContent(resultSet.getString("content"));
                content.setP_time(DateUtil.timestamp2String(resultSet
                        .getTimestamp("p_time")));
                content.setUp(resultSet.getInt("up"));
                content.setDown(resultSet.getInt("down"));
                content.setScore(resultSet.getDouble("score"));
                content.setUid(resultSet.getLong("uid"));
                content.setLogin_id(resultSet.getString("login_id"));
                content.setPic_url(resultSet.getString("pic_url"));
                content.setContent_num(resultSet.getInt("content_num"));
                content.setF_num(resultSet.getInt("f_num"));
                content.setType(0);
                result.add(content);
            }
            resultSet.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return result;
    }

    /**
     * 获取id小于max_id的最新(Constents.CONTENT_PERTIME_NUM条)的内容
     * 用于登录用户
     * 
     * @param uid
     * @param max_id
     * @return List<ExtendedContent>
     */
    public List<ExtendedContent> loadSpecialContent(Long uid, Long max_id) {
        List<ExtendedContent> result = new ArrayList<ExtendedContent>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "SELECT b.content_num, b.f_num, b.id as uid, b.login_id, b.pic_url,a.score, a.id as cid, a.content, a.up, a.down, a.p_time,( SELECT c.type FROM `upanddown` c WHERE c.uid = ? AND c.cid = a.id ) type FROM `content` a, `user` b WHERE a.uid = b.id  AND a.id < ? ORDER BY a.id DESC LIMIT 0, ?";
            System.out.println(sql);
            pst = conn.prepareStatement(sql);
            pst.setLong(1, uid);
            pst.setLong(2, max_id);
            pst.setInt(3, Constants.CONTENT_PERTIME_NUM);
            ResultSet resultSet = pst.executeQuery();
            ExtendedContent content;
            while (resultSet.next()) {
                content = new ExtendedContent();
                content.setCid(resultSet.getLong("cid"));
                content.setContent(resultSet.getString("content"));
                content.setP_time(DateUtil.timestamp2String(resultSet
                        .getTimestamp("p_time")));
                content.setUp(resultSet.getInt("up"));
                content.setDown(resultSet.getInt("down"));
                content.setScore(resultSet.getDouble("score"));
                content.setUid(resultSet.getLong("uid"));
                content.setLogin_id(resultSet.getString("login_id"));
                content.setPic_url(resultSet.getString("pic_url"));
                content.setContent_num(resultSet.getInt("content_num"));
                content.setF_num(resultSet.getInt("f_num"));
                content.setType(resultSet.getInt("type"));
                result.add(content);
            }
            resultSet.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return result;
    }

    /**
     * 获取id大于min_id的比较新(Constents.CONTENT_PERTIME_NUM条)的内容
     * 用于未登录用户
     * 
     * @param min_id
     * @return List<ExtendedContent>
     */
    public List<ExtendedContent> loadLastestContent(Long min_id) {
        List<ExtendedContent> result = new ArrayList<ExtendedContent>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "SELECT b.content_num,b.f_num, b.id as uid, b.login_id, b.pic_url,a.score, a.id as cid, a.content, a.up, a.down, a.p_time FROM `content` a, `user` b WHERE a.uid = b.id AND a.id > ? ORDER BY a.id asc LIMIT 0, ?";
            pst = conn.prepareStatement(sql);
            pst.setLong(1, min_id);
            pst.setInt(2, Constants.CONTENT_PERTIME_NUM);
            ResultSet resultSet = pst.executeQuery();
            ExtendedContent content;
            while (resultSet.next()) {
                content = new ExtendedContent();
                content.setCid(resultSet.getLong("cid"));
                content.setContent(resultSet.getString("content"));
                content.setP_time(DateUtil.timestamp2String(resultSet
                        .getTimestamp("p_time")));
                content.setUp(resultSet.getInt("up"));
                content.setDown(resultSet.getInt("down"));
                content.setScore(resultSet.getDouble("score"));
                content.setUid(resultSet.getLong("uid"));
                content.setLogin_id(resultSet.getString("login_id"));
                content.setPic_url(resultSet.getString("pic_url"));
                content.setContent_num(resultSet.getInt("content_num"));
                content.setF_num(resultSet.getInt("f_num"));
                content.setType(0);
                result.add(content);
            }
            resultSet.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return result;
    }

    /**
     * 获取id大于min_id的比较新(Constents.CONTENT_PERTIME_NUM条)的内容
     * 用于登录用户
     * 
     * @param uid
     * @param min_id
     * @return List<ExtendedContent>
     */
    public List<ExtendedContent> loadLastestContent(Long uid, Long min_id) {
        List<ExtendedContent> result = new ArrayList<ExtendedContent>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "SELECT b.content_num, b.f_num, b.id as uid, b.login_id, b.pic_url,a.score, a.id as cid, a.content, a.up, a.down, a.p_time,( SELECT c.type FROM `upanddown` c WHERE c.uid = ? AND c.cid = a.id ) type FROM `content` a, `user` b WHERE a.uid = b.id AND a.id > ? ORDER BY a.id ASC LIMIT 0, ?";
            System.out.println(sql);
            pst = conn.prepareStatement(sql);
            pst.setLong(1, uid);
            pst.setLong(2, min_id);
            pst.setInt(3, Constants.CONTENT_PERTIME_NUM);
            ResultSet resultSet = pst.executeQuery();
            ExtendedContent content;
            while (resultSet.next()) {
                content = new ExtendedContent();
                content.setCid(resultSet.getLong("cid"));
                content.setContent(resultSet.getString("content"));
                content.setP_time(DateUtil.timestamp2String(resultSet
                        .getTimestamp("p_time")));
                content.setUp(resultSet.getInt("up"));
                content.setDown(resultSet.getInt("down"));
                content.setScore(resultSet.getDouble("score"));
                content.setUid(resultSet.getLong("uid"));
                content.setLogin_id(resultSet.getString("login_id"));
                content.setPic_url(resultSet.getString("pic_url"));
                content.setContent_num(resultSet.getInt("content_num"));
                content.setF_num(resultSet.getInt("f_num"));
                content.setType(resultSet.getInt("type"));
                result.add(content);
            }
            resultSet.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return result;
    }

    /**
     * 获取最热内容列表
     * @param uid
     * @param p
     * @return List<ExtendedContent>
     */
    public List<ExtendedContent> loadHotestContent(Long uid, int p) {
        List<ExtendedContent> result = new ArrayList<ExtendedContent>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "SELECT b.content_num, b.f_num, b.id AS uid, b.login_id, b.pic_url, a.score, a.id AS cid, a.content, a.up, a.down, a.p_time, ( SELECT c.type FROM `upanddown` c WHERE c.uid = ? AND c.cid = a.id ) type FROM `content` a, `user` b WHERE a.uid = b.id ORDER BY a.score DESC LIMIT ?,?";
            System.out.println(sql);
            pst = conn.prepareStatement(sql);
            pst.setLong(1, uid);
            pst.setInt(2, (p-1));
            pst.setInt(3, Constants.CONTENT_PERTIME_NUM);
            ResultSet resultSet = pst.executeQuery();
            ExtendedContent content;
            while (resultSet.next()) {
                content = new ExtendedContent();
                content.setCid(resultSet.getLong("cid"));
                content.setContent(resultSet.getString("content"));
                content.setP_time(DateUtil.timestamp2String(resultSet
                        .getTimestamp("p_time")));
                content.setUp(resultSet.getInt("up"));
                content.setDown(resultSet.getInt("down"));
                content.setScore(resultSet.getDouble("score"));
                content.setUid(resultSet.getLong("uid"));
                content.setLogin_id(resultSet.getString("login_id"));
                content.setPic_url(resultSet.getString("pic_url"));
                content.setContent_num(resultSet.getInt("content_num"));
                content.setF_num(resultSet.getInt("f_num"));
                content.setType(resultSet.getInt("type"));
                result.add(content);
            }
            resultSet.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return result;
    }
    
    /**
     * 刷新内容积分
     */
    public static void refreshScore(){
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = C3p0Util.getConn();
            String sql = "update content a set a.score=(a.up+100)/(a.up+a.down+"+Constants.SCORE_BASE+")";
            pst = conn.prepareStatement(sql);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        
    }
}
